package cn.mfeg.servlet;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import cn.mfeg.models.CollegesUser;
import cn.mfeg.models.DepartmentUser;
import cn.mfeg.models.Project;
import cn.mfeg.services.CollegesUserService;
import cn.mfeg.services.DepartmentUserService;
import cn.mfeg.services.ProjectService;
import cn.mfeg.utils.ExportExcelUtil;
import cn.mfeg.utils.ObjectValidateUtil;

public class ExportDCProjectServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	ApplicationContext applicationContext;
	ProjectService projectService;
	CollegesUserService collegesUserService;
	DepartmentUserService departmentUserService;
	
	public void init() throws ServletException {
		applicationContext = new ClassPathXmlApplicationContext("spring-servlet.xml");
		projectService = (ProjectService) applicationContext.getBean(ProjectService.class);
		collegesUserService = (CollegesUserService) applicationContext.getBean(CollegesUserService.class);
		departmentUserService = (DepartmentUserService) applicationContext.getBean(DepartmentUserService.class);
	}
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		 List<CollegesUser> collegesUsers = collegesUserService.selectAllCollegesUserInfo();
		 List<DepartmentUser> departmentUsers = departmentUserService.selectAllDepartmentUserInfo();
		 Map<String, Object> map = new HashMap<>();
		 for (CollegesUser collegesUser:collegesUsers) {
			 map.put("organiser", collegesUser.getCode());
			 map.put("organiserRole", "2");
			List<Project> projects = projectService.selectAllProjectInfoByOrganiserOrganiserRole(map);
			collegesUser.setProjects(projects);
		 }
		 for (DepartmentUser departmentUser:departmentUsers) {
			 map.put("organiser", departmentUser.getUserName());
			 map.put("organiserRole", "4");
			 List<Project> projects = projectService.selectAllProjectInfoByOrganiserOrganiserRole(map);
			 departmentUser.setProjects(projects);
		 }
		//创建工作空间
		HSSFWorkbook book = new HSSFWorkbook();
		// 创建一个工作表
		HSSFSheet sheet = book.createSheet("处室、高校项目报表");
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		// 创建一个单元格 设置值
		HSSFCell cel = null;
		cel = row.createCell(0);
		cel.setCellValue("学校名");
		cel = row.createCell(1);
		cel.setCellValue("创建用户");
		cel = row.createCell(2);
		cel.setCellValue("项目名称");
		cel = row.createCell(3);
		cel.setCellValue("项目日期");
		cel = row.createCell(4);
		cel.setCellValue("项目状态");
		cel = row.createCell(5);
		cel.setCellValue("主办单位");
		cel = row.createCell(6);
		cel.setCellValue("项目官网");
		cel = row.createCell(7);
		cel.setCellValue("联系电话");
		cel = row.createCell(8);
		cel.setCellValue("需要专家人数");
		cel = row.createCell(9);
		cel.setCellValue("已有专家人数");
		cel = row.createCell(10);
		cel.setCellValue("优秀专家人数");
		int rowIndex = 1;
		for (CollegesUser c:collegesUsers) {
			int size = c.getProjects().size();
			//合并单元格  CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
			if (ObjectValidateUtil.objIsNull(c.getProjects())||c.getProjects().size()==0) {
				CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, 10);
				row = sheet.createRow(rowIndex++);
				cel = row.createCell(0);
				cel.setCellValue(c.getName());
				cel = row.createCell(1);
				cel.setCellValue("无");
				sheet.addMergedRegion(region);
			}else {
				if (size>1) {
					CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex+size-1, 0, 0);
					row = sheet.createRow(rowIndex);
					sheet.addMergedRegion(region);
				}
			}
			for(Project p:c.getProjects()){
				row = sheet.createRow(rowIndex++);
				cel = row.createCell(0);
				cel.setCellValue(c.getName());
				cel = row.createCell(1);
				cel.setCellValue(p.getOrganiser());
				cel = row.createCell(2);
				cel.setCellValue(p.getProjectname());
				cel = row.createCell(3);
				cel.setCellValue(p.getStarttime()+"~"+p.getEndtime());
				cel = row.createCell(4);
				cel.setCellValue(p.getStatus());
				cel = row.createCell(5);
				cel.setCellValue(p.getHostunit());
				cel = row.createCell(6);
				cel.setCellValue(p.getWebsite());
				cel = row.createCell(7);
				cel.setCellValue(p.getPhonenum());
				cel = row.createCell(8);
				cel.setCellValue(p.getExperttotal());
				cel = row.createCell(9);
				cel.setCellValue("XXX");
				cel = row.createCell(10);
				cel.setCellValue("XXX");
			}
		}
		if (!ObjectValidateUtil.objIsNull(departmentUsers)&&departmentUsers.size()!=0) {
			row = sheet.createRow(rowIndex++);
			cel = row.createCell(0);
			cel.setCellValue("处室名");
			cel = row.createCell(1);
			cel.setCellValue("创建用户");
			cel = row.createCell(2);
			cel.setCellValue("项目名称");
			cel = row.createCell(3);
			cel.setCellValue("项目日期");
			cel = row.createCell(4);
			cel.setCellValue("项目状态");
			cel = row.createCell(5);
			cel.setCellValue("主办单位");
			cel = row.createCell(6);
			cel.setCellValue("项目官网");
			cel = row.createCell(7);
			cel.setCellValue("联系电话");
			cel = row.createCell(8);
			cel.setCellValue("需要专家人数");
			cel = row.createCell(9);
			cel.setCellValue("已有专家人数");
			cel = row.createCell(10);
			cel.setCellValue("优秀专家人数");
		}
		
		for (DepartmentUser d:departmentUsers) {
			int size = d.getProjects().size();
			if (ObjectValidateUtil.objIsNull(d.getProjects())||d.getProjects().size()==0) {
				CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, 10);
				row = sheet.createRow(rowIndex++);
				cel = row.createCell(0);
				cel.setCellValue(d.getDepartmentName());
				cel = row.createCell(1);
				cel.setCellValue("无");
				sheet.addMergedRegion(region);
			}else {
				CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex+size-1, 0, 0);
				row = sheet.createRow(rowIndex);
				sheet.addMergedRegion(region);
			}
			for(Project p:d.getProjects()){
				row = sheet.createRow(rowIndex++);
				cel = row.createCell(0);
				cel.setCellValue(d.getDepartmentName());
				cel = row.createCell(1);
				cel.setCellValue(p.getOrganiser());
				cel = row.createCell(2);
				cel.setCellValue(p.getProjectname());
				cel = row.createCell(3);
				cel.setCellValue(p.getStarttime()+"~"+p.getEndtime());
				cel = row.createCell(4);
				cel.setCellValue(p.getStatus());
				cel = row.createCell(5);
				cel.setCellValue(p.getHostunit());
				cel = row.createCell(6);
				cel.setCellValue(p.getWebsite());
				cel = row.createCell(7);
				cel.setCellValue(p.getPhonenum());
				cel = row.createCell(8);
				cel.setCellValue(p.getExperttotal());
				cel = row.createCell(9);
				cel.setCellValue("XXX");
				cel = row.createCell(10);
				cel.setCellValue("XXX");
			}
		}
		ExportExcelUtil.exportExcel(req, resp, book);
	}
}
